﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Xml;

namespace Pasteles
{
    public partial class ModifyCake : Form
    {
        public DataTable db = new DataTable(); //db0, sQuery0
        public DataTable dbTemp = new DataTable(); //db0, sQuery0
        public DataTable db1 = new DataTable(); //db1, sQuery1
        public DataTable db2 = new DataTable(); //db2, sQuery2
        public DataTable db3 = new DataTable(); //db3, sQuery3
        public DataTable addDB = new DataTable();
        MySqlConnection connection;
        public static int idstatic=-1;//ma banh
        public static int maloaibanh;//ma loai banh
        string sConfig_hostname;
        string sConfig_dbname;
        string sConfig_dbusername;
        string sConfig_dbpassword;
        string sConfig_SerialPort;
        string sConfig_Baudrate;
        string sConfig_Parity;
        string sConfig_Datalength;
        
        
        string sConfig_line1 = "";
        string sConfig_line2 = "";
        string sConfig_line3 = "";
        string sConfig_line4 = "";
        string sConfig_line5 = "";

        public ModifyCake()
        {
            InitializeComponent();
            readConfigurationFile();
            preparingMySQL();
            this.createConection();

        }

        private void preparingMySQL()
        {

            string MyConString = "SERVER=" + sConfig_hostname + ";" +
                "DATABASE=" + sConfig_dbname + ";" +
                "UID=" + sConfig_dbusername + ";" +
                "PASSWORD=" + sConfig_dbpassword + ";";
            connection = new MySqlConnection(MyConString);
            //MySqlCommand command = connection.CreateCommand();
            //MySqlDataReader Reader;
            //connection
            try
            {
                connection.Open();
                // MessageBox.Show("Connect to MySQL OK!");

            }
            catch (Exception error)
            {
                MessageBox.Show("Connection Error " + error.ToString());
            }
            connection.Close();
        }

        private void readConfigurationFile()
        {
            XmlReader reader = XmlReader.Create("config.xml");
            while (reader.Read())
            {
                if (reader.NodeType == XmlNodeType.Element
                    && reader.Name == "Pasteles")
                {
                    /*MessageBox.Show("hostname= " + reader.GetAttribute(0));
                    MessageBox.Show("dbname= " + reader.GetAttribute(1));
                    MessageBox.Show("dbusername= " + reader.GetAttribute(2));
                    MessageBox.Show("dbpassword= " + reader.GetAttribute(3));*/
                    sConfig_hostname = reader.GetAttribute(0);
                    sConfig_dbname = reader.GetAttribute(1);
                    sConfig_dbusername = reader.GetAttribute(2);
                    sConfig_dbpassword = reader.GetAttribute(3);

                    while (reader.NodeType != XmlNodeType.EndElement)
                    {
                        reader.Read();
                        if (reader.Name == "Printing")
                        {
                            /*MessageBox.Show("SerialPort= " + reader.GetAttribute(0));
                            MessageBox.Show("Baudrate= " + reader.GetAttribute(1));
                            MessageBox.Show("Parity= " + reader.GetAttribute(2));
                            MessageBox.Show("Datalength= " + reader.GetAttribute(3));
                            MessageBox.Show("Stopbits= " + reader.GetAttribute(4));*/
                            sConfig_SerialPort = reader.GetAttribute(0);
                            sConfig_Baudrate = reader.GetAttribute(1);
                            sConfig_Parity = reader.GetAttribute(2);
                            sConfig_Datalength = reader.GetAttribute(3);
                            //sConfig_Stopbits=reader.GetAttribute(4);

                            while (reader.NodeType != XmlNodeType.EndElement)
                            {
                                reader.Read();
                                if (reader.Name == "line1")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line1 = " + reader.Value);
                                            sConfig_line1 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if

                                if (reader.Name == "line2")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line2 = " + reader.Value);
                                            sConfig_line2 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if
                                if (reader.Name == "line3")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line3 = " + reader.Value);
                                            sConfig_line3 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if
                                if (reader.Name == "line4")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line4 = " + reader.Value);
                                            sConfig_line4 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if
                                if (reader.Name == "line5")
                                {
                                    while (reader.NodeType != XmlNodeType.EndElement)
                                    {
                                        reader.Read();
                                        if (reader.NodeType == XmlNodeType.Text)
                                        {
                                            //MessageBox.Show("line5 = " + reader.Value);
                                            sConfig_line5 = reader.Value;
                                        }
                                    }
                                    reader.Read();
                                } //end if
                            }
                        } //end if
                    } //end while
                } //end if
            } //end while
            reader.Close();
        }

        private void loadBanhFromMySQLtoDataTable(String sQuery, ref DataTable datatable)
        {
            try
            {
                connection.Open();
                MySqlDataAdapter myDA = new MySqlDataAdapter();
                myDA.SelectCommand = new MySqlCommand(sQuery, connection);
                                
                datatable.Columns.Add("Mã Bánh");
                datatable.PrimaryKey = new DataColumn[] { datatable.Columns["ID"] };
                datatable.Columns.Add("Tên Bánh");
                datatable.Columns.Add("Hạn Sử Dụng(Ngày)");
                datatable.Columns.Add("Giá Bán");
                
                datatable.Columns.Add("Giá Sản Xuất");
                datatable.Columns.Add("Sale Off");

                //myDA.Fill(datatable);

                var dataReader = myDA.SelectCommand.ExecuteReader();
                while (dataReader.Read())
                {
                    try
                    {
                        
                        datatable.Rows.Add(dataReader.GetInt32(0),
                                           dataReader.GetString(1),
                                           dataReader.GetInt32(2), dataReader.GetInt32(3), dataReader.GetInt32(4), dataReader.GetFloat(5));
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Push data error: " + ex.ToString());
                    }
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("LoadBanhFromMySQLtoDataTable error: " + ex.ToString());
            }

        }

        private void createConection()
        {

            string sQuery0 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where DA_XOA=0 and MA_LOAI_BANH=1;";
            string sQuery1 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where DA_XOA=0 and MA_LOAI_BANH=2;";
            string sQuery2 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where DA_XOA=0 and MA_LOAI_BANH=3;";
            string sQuery3 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where DA_XOA=0 and MA_LOAI_BANH=4;";
            try
            {
                loadBanhFromMySQLtoDataTable(sQuery0, ref db);
                loadBanhFromMySQLtoDataTable(sQuery1, ref db1);
                loadBanhFromMySQLtoDataTable(sQuery2, ref db2);
                loadBanhFromMySQLtoDataTable(sQuery3, ref db3);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("SQL Error: " + ex.Message);
            }
            //addDB = db.Clone(); //DataTable 
            //addDB.Columns.Add("Quantity", typeof(int));
            //addDB.Columns.Add("Cost", typeof(double));
            //addDB.Columns.Add("Off (%)", typeof(double));
            //ModifyDataGrid.DataSource = addDB;
            DataGridViewButtonColumn btn_mod = new DataGridViewButtonColumn();
            btn_mod.HeaderText = "Modify";
            btn_mod.Name = "Modify";
            btn_mod.Text = "Modify";
            btn_mod.ToolTipText = "Modify";
            btn_mod.UseColumnTextForButtonValue = true;
           // ModifyDataGrid.Columns.Add(btn_mod);
            ModifyDataGrid.Columns.Insert(1, btn_mod);
            ModifyDataGrid.Columns[1].ReadOnly = true;//cot id banh
            ModifyDataGrid.AutoSizeColumnsMode= DataGridViewAutoSizeColumnsMode.AllCells;
        }
        






        private void btnCake_Click(object sender, EventArgs e)
        {
            btn_AddCake.Enabled = true;
            //dbTemp = db1.Copy();
            db1.PrimaryKey = new DataColumn[] { db1.Columns["ID"] };
            ModifyDataGrid.DataSource = db1;
            maloaibanh = 2;
        }

        private void ModifyCake_FormClosing(object sender, FormClosingEventArgs e)
        {
            
            
        }

        private void btnBread_Click(object sender, EventArgs e)
        {
            btn_AddCake.Enabled = true;
            //dbTemp = db.Copy();
            db.PrimaryKey = new DataColumn[] { db.Columns["ID"] };
            ModifyDataGrid.DataSource = db;
            maloaibanh = 1;
        }

        private void btnSand_Click(object sender, EventArgs e)
        {
            btn_AddCake.Enabled = true;
            //dbTemp = db2.Copy();
            db2.PrimaryKey = new DataColumn[] { db2.Columns["ID"] };
            ModifyDataGrid.DataSource = db2;
            maloaibanh = 3;
        }

        private void btnWater_Click(object sender, EventArgs e)
        {
            btn_AddCake.Enabled = true;
            //dbTemp = db3.Copy();
            db3.PrimaryKey = new DataColumn[] { db3.Columns["ID"] };
            ModifyDataGrid.DataSource = db3;
            maloaibanh = 4;
        }

        private void btn_AddCake_Click(object sender, EventArgs e)
        {
            Add_Cake add_form = new Add_Cake();
            idstatic = -1;
            add_form.ShowDialog(this);

            db = new DataTable();
            db1 = new DataTable();
            db2 = new DataTable();
            db3 = new DataTable();
            ModifyDataGrid.Columns.Remove("Modify");
            createConection();
            //MySqlDataAdapter myDA = new MySqlDataAdapter();
            switch (maloaibanh)
            {
                case 1:

                    //string sQuery0 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where MA_LOAI_BANH=1;";
                    //connection.Open();

                    //myDA.SelectCommand = new MySqlCommand(sQuery0, connection);
                    //myDA.Update(db);
                    ModifyDataGrid.DataSource = db;

                    break;
                case 2:
                    //string sQuery1 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where MA_LOAI_BANH=2;";
                    //connection.Open();
                    ////MySqlDataAdapter myDA = new MySqlDataAdapter();
                    //myDA.SelectCommand = new MySqlCommand(sQuery1, connection);
                    //myDA.Update(db1);
                    ModifyDataGrid.DataSource = db1;
                    break;
                case 3:
                    //string sQuery2 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where MA_LOAI_BANH=3;";
                    //connection.Open();
                    ////MySqlDataAdapter myDA = new MySqlDataAdapter();
                    //myDA.SelectCommand = new MySqlCommand(sQuery2, connection);
                    //myDA.Update(db2);
                    ModifyDataGrid.DataSource = db2;
                    break;
                case 4:
                    //string sQuery3 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where MA_LOAI_BANH=4;";
                    //connection.Open();
                    ////MySqlDataAdapter myDA = new MySqlDataAdapter();
                    //myDA.SelectCommand = new MySqlCommand(sQuery3, connection);
                    //myDA.Update(db3);
                    ModifyDataGrid.DataSource = db3;
                    break;
            }
            

            
        }

        private void ModifyDataGrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try 
            {
                
                

                string id =ModifyDataGrid.CurrentRow.Cells[2].Value.ToString();
                
                
                if (e.ColumnIndex == 0)
                {
                    DialogResult myDialogResult;
                    myDialogResult = MessageBox.Show("Are you sure?", "Delete Cake", MessageBoxButtons.YesNo);
                    if (myDialogResult == DialogResult.Yes)
                    {
                        connection.Open();
                        MySqlDataAdapter myDA = new MySqlDataAdapter();
                        string sQuery = "call xoa_banh(" + id + ")";

                        myDA.SelectCommand = new MySqlCommand(sQuery, connection);
                        myDA.SelectCommand.ExecuteReader();
                        ModifyDataGrid.Rows.RemoveAt(e.RowIndex);
                        
                        //MessageBox.Show(sQuery);
                        
                        
                        connection.Close();
                        
                        
                    }
                    else {}
                }
                else if (e.ColumnIndex == 1)
                {
                    Add_Cake add = new Add_Cake();
                    add.Text = "Pasteles Bakery - Modify Cake";
                    idstatic = Convert.ToInt16(id);
                    
                    add.ShowDialog();                                   
                                                        
                    //try
                    //{
                    //    loadBanhFromMySQLtoDataTable(sQuery0, ref db);
                    //    loadBanhFromMySQLtoDataTable(sQuery1, ref db1);
                    //    loadBanhFromMySQLtoDataTable(sQuery2, ref db2);
                    //    loadBanhFromMySQLtoDataTable(sQuery3, ref db3);
                    //}
                    //catch (MySqlException ex)
                    //{
                    //    MessageBox.Show("SQL Error: " + ex.Message);
                    //}
                    db = new DataTable();
                    db1 = new DataTable();
                    db2 = new DataTable();
                    db3 = new DataTable();
                    ModifyDataGrid.Columns.Remove("Modify");
                    createConection();
                    //MySqlDataAdapter myDA = new MySqlDataAdapter();
                    switch(maloaibanh)
                    {
                        case 1:

                            //string sQuery0 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where MA_LOAI_BANH=1;";
                            //connection.Open();
                            
                            //myDA.SelectCommand = new MySqlCommand(sQuery0, connection);
                            //myDA.Update(db);
                            ModifyDataGrid.DataSource = db;
                            
                            break;
                        case 2:
                            //string sQuery1 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where MA_LOAI_BANH=2;";
                            //connection.Open();
                            ////MySqlDataAdapter myDA = new MySqlDataAdapter();
                            //myDA.SelectCommand = new MySqlCommand(sQuery1, connection);
                            //myDA.Update(db1);
                            ModifyDataGrid.DataSource = db1; 
                            break;
                        case 3:
                            //string sQuery2 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where MA_LOAI_BANH=3;";
                            //connection.Open();
                            ////MySqlDataAdapter myDA = new MySqlDataAdapter();
                            //myDA.SelectCommand = new MySqlCommand(sQuery2, connection);
                            //myDA.Update(db2);
                            ModifyDataGrid.DataSource = db2; 
                            break;
                        case 4:
                            //string sQuery3 = "select MA_BANH, TEN_BANH,HAN_SU_DUNG, GIA_KINH_DOANH, GIA_SAN_XUAT, SALE_OFF from banh where MA_LOAI_BANH=4;";
                            //connection.Open();
                            ////MySqlDataAdapter myDA = new MySqlDataAdapter();
                            //myDA.SelectCommand = new MySqlCommand(sQuery3, connection);
                            //myDA.Update(db3);
                            ModifyDataGrid.DataSource = db3; 
                            break;
                    }
                    //connection.Close();

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void btn_Refresh_Click(object sender, EventArgs e)
        {
            


        }

        private void ModifyCake_Load(object sender, EventArgs e)
        {

        }


    }
}
